﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace TiecCuoi
{
    public partial class TraCuu : Form
    {
        string _MaHD;
        bool _DeleteError = false;
        //Data base
        private SqlConnection _CNN;
        public TraCuu(SqlConnection cnn)
        {
            _CNN = cnn;
            InitializeComponent();
        }
        //Tim theo ten chu re
        private void TB_TenChuRe_TextChanged(object sender, EventArgs e)
        {
            L_viewKQ.Items.Clear();
            string tenchure;
            tenchure = TB_TenChuRe.Text.Trim();
            ListViewItem item;
            bool bool_mau = false;
            try
            {
                string cmd = "select MaTiec,TenChuRe,TeCoDau,NgayDaiTiec from TIECCUOI where TenChuRe like '%" + tenchure+ "%'";
                SqlCommand com = new SqlCommand(cmd,_CNN);
                SqlDataReader read = com.ExecuteReader();
                while (read.Read())
                {
                    item = new ListViewItem(read[0].ToString());
                    item.SubItems.Add(read[1].ToString());
                    item.SubItems.Add(read[2].ToString());
                    item.SubItems.Add(read[3].ToString());
                    if (bool_mau == false)
                        item.BackColor = Color.White;
                    else
                        item.BackColor = Color.FromArgb(245, 204, 102);
                    bool_mau = !bool_mau;
                    L_viewKQ.Items.Add(item);
 
                }
                read.Close();
            }
            catch (Exception er)
            {

                MessageBox.Show(er.Message);
            }
        }

        private void B_TimRe_Click(object sender, EventArgs e)
        {
            L_viewKQ.Items.Clear();
            string tenchure;
            tenchure = TB_TenChuRe.Text.Trim();
            ListViewItem item;
            bool bool_mau = false;
            try
            {
                string cmd;
                if(tenchure == "")
                {
                    cmd = "select MaTiec,TenChuRe,TeCoDau,NgayDaiTiec from TIECCUOI";
                }
                else
                {
                    cmd = "select MaTiec,TenChuRe,TeCoDau,NgayDaiTiec from TIECCUOI where TenChuRe like '%" + tenchure + "%'";
                }

                SqlCommand com = new SqlCommand(cmd, _CNN);
                SqlDataReader read = com.ExecuteReader();
               while (read.Read())
                {
                    item = new ListViewItem(read[0].ToString());
                    item.SubItems.Add(read[1].ToString());
                    item.SubItems.Add(read[2].ToString());
                    item.SubItems.Add(read[3].ToString());
                    if (bool_mau == false)
                        item.BackColor = Color.White;
                    else
                        item.BackColor = Color.FromArgb(245, 204, 102);
                    bool_mau = !bool_mau;
                    L_viewKQ.Items.Add(item);

                }
                read.Close();
            }
            catch (Exception er)
            {

                MessageBox.Show(er.Message);
            }
        }

        //Tim theo ten co dau
        private void textBox3_TextChanged(object sender, EventArgs e)
        {
            L_viewKQ.Items.Clear();
            string tencodau;
            tencodau = textBox3.Text.Trim(); L_viewKQ.Items.Clear();
            ListViewItem item;
            bool bool_mau = false;
            try
            {
                string cmd = "select MaTiec,TenChuRe,TeCoDau,NgayDaiTiec from TIECCUOI where TeCoDau like '%" + tencodau + "%'";
                SqlCommand com = new SqlCommand(cmd, _CNN);
                SqlDataReader read = com.ExecuteReader();
                while (read.Read())
                {
                    item = new ListViewItem(read[0].ToString());
                    item.SubItems.Add(read[1].ToString());
                    item.SubItems.Add(read[2].ToString());
                    item.SubItems.Add(read[3].ToString());
                    if (bool_mau == false)
                        item.BackColor = Color.White;
                    else
                        item.BackColor = Color.FromArgb(245, 204, 102);
                    bool_mau = !bool_mau;
                    L_viewKQ.Items.Add(item);

                }
                read.Close();
            }
            catch (Exception er)
            {

                MessageBox.Show(er.Message);
            }
        }

        private void B_TimDau_Click(object sender, EventArgs e)
        {
            L_viewKQ.Items.Clear();
            string tencodau;
            tencodau = textBox3.Text.Trim(); L_viewKQ.Items.Clear();
            ListViewItem item;
            bool bool_mau = false;
            try
            {
                string cmd = "select MaTiec,TenChuRe,TeCoDau,NgayDaiTiec from TIECCUOI where TeCoDau like '%" + tencodau + "%'";
                SqlCommand com = new SqlCommand(cmd, _CNN);
                SqlDataReader read = com.ExecuteReader();
                while (read.Read())
                {
                    item = new ListViewItem(read[0].ToString());
                    item.SubItems.Add(read[1].ToString());
                    item.SubItems.Add(read[2].ToString());
                    item.SubItems.Add(read[3].ToString());
                    if (bool_mau == false)
                        item.BackColor = Color.White;
                    else
                        item.BackColor = Color.FromArgb(245, 204, 102);
                    bool_mau = !bool_mau;
                    L_viewKQ.Items.Add(item);

                }
                read.Close();
            }
            catch (Exception er)
            {

                MessageBox.Show(er.Message);
            }
        }
        //tim theo ma tiec
        private void TB_MaTiecCuoi_TextChanged(object sender, EventArgs e)
        {

            L_viewKQ.Items.Clear();
            string matiec;
            matiec = TB_MaTiecCuoi.Text.Trim();
            ListViewItem item;
            bool bool_mau = false;
            try
            {
                string cmd = "select MaTiec,TenChuRe,TeCoDau,NgayDaiTiec from TIECCUOI where MaTiec like '%" + matiec + "%'";
                SqlCommand com = new SqlCommand(cmd, _CNN);
                SqlDataReader read = com.ExecuteReader();
                while (read.Read())
                {
                    item = new ListViewItem(read[0].ToString());
                    item.SubItems.Add(read[1].ToString());
                    item.SubItems.Add(read[2].ToString());
                    item.SubItems.Add(read[3].ToString());
                    if (bool_mau == false)
                        item.BackColor = Color.White;
                    else
                        item.BackColor = Color.FromArgb(245, 204, 102);
                    bool_mau = !bool_mau;
                    L_viewKQ.Items.Add(item);

                }
                read.Close();
            }
            catch (Exception er)
            {

                MessageBox.Show(er.Message);
            }
        }

        private void B_TimMA_Click(object sender, EventArgs e)
        {
            L_viewKQ.Items.Clear();
            string matiec;
            matiec = TB_MaTiecCuoi.Text.Trim();
            ListViewItem item;
            bool bool_mau = false;
            try
            {
                string cmd = "select MaTiec,TenChuRe,TeCoDau,NgayDaiTiec from TIECCUOI where MaTiec like '%" + matiec + "%'";
                SqlCommand com = new SqlCommand(cmd, _CNN);
                SqlDataReader read = com.ExecuteReader();
                while (read.Read())
                {
                    item = new ListViewItem(read[0].ToString());
                    item.SubItems.Add(read[1].ToString());
                    item.SubItems.Add(read[2].ToString());
                    item.SubItems.Add(read[3].ToString());
                    if (bool_mau == false)
                        item.BackColor = Color.White;
                    else
                        item.BackColor = Color.FromArgb(245, 204, 102);
                    bool_mau = !bool_mau;
                    L_viewKQ.Items.Add(item);

                }
                read.Close();
            }
            catch (Exception er)
            {

                MessageBox.Show(er.Message);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if(L_viewKQ.FocusedItem == null)
            {
                MessageBox.Show("Vui lòng chọn tiệc cần xóa trước", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.None);
                return;
            }
            string matiec = L_viewKQ.Items[L_viewKQ.FocusedItem.Index].SubItems[0].Text.Trim();
           //XOA HoaDon
            XoaHoaDon(matiec);
            //Xoa ChiTietDatTiec
            XoaCTTiecCuoi(matiec);
            //Xoa ChiTietDichVu
            XoaChiTietDichVu(matiec);
            //Xoa ChiTietMonAn
            XoaChiTietMonAn(matiec);
            //Xoa dat ban
            XoaDatBan(matiec);
            //Xoa TiecCuoi
            XoaTiecCuoi(matiec);
           
            if(_DeleteError)
            {
                MessageBox.Show("Xóa thất bại", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                MessageBox.Show("Xóa thành công", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.None);
                L_viewKQ.Items.Remove(L_viewKQ.FocusedItem);
                L_viewDV.Items.Clear();
                listMA.Items.Clear();
                ReSetAllTextBox();
            }
            _DeleteError = false;

        }
//-----------------------------------------------------------------------------------------------------------------
//XOA DAT TIEC
//------------------------------------------------------------------------------------------------------------------
        private void XoaChiTietMonAn(string MaTiec)
        {
            try
            {

                string cmd = "DELETE FROM CHITIETMONAN WHERE MADATBAN IN (SELECT MADATBAN FROM TIECCUOI,DATBAN WHERE TIECCUOI.MATIEC = DATBAN.MATIEC AND TIECCUOI.MATIEC = @MATIEC)";
                SqlCommand cmddelete = new SqlCommand(cmd, _CNN);
                cmddelete.CommandType = CommandType.Text;
                cmddelete.Parameters.AddWithValue("@MATIEC", MaTiec);
                cmddelete.ExecuteNonQuery();
                cmddelete.Dispose();
            }
            catch (Exception e5)
            {
                MessageBox.Show(e5.Message);
                _DeleteError = true;
            }

        }
        private void XoaChiTietDichVu(string MaTiec)
        {
            try
            {

                string cmd = "DELETE FROM CHITIETDICHVU WHERE MADATBAN IN (SELECT MADATBAN FROM TIECCUOI,DATBAN WHERE TIECCUOI.MATIEC = DATBAN.MATIEC AND TIECCUOI.MATIEC = @MATIEC)";
                SqlCommand cmddelete = new SqlCommand(cmd, _CNN);
                cmddelete.CommandType = CommandType.Text;
                cmddelete.Parameters.AddWithValue("@MATIEC", MaTiec);
                cmddelete.ExecuteNonQuery();
                cmddelete.Dispose();
            }
            catch (Exception e5)
            {
                MessageBox.Show(e5.Message);
                _DeleteError = true;
            }
        }
        private void XoaDatBan(string MaTiec)
        {
            try
            {

                string cmd = "DELETE FROM DATBAN WHERE MATIEC = @MATIEC";
                SqlCommand cmddelete = new SqlCommand(cmd, _CNN);
                cmddelete.CommandType = CommandType.Text;
                cmddelete.Parameters.AddWithValue("@MATIEC", MaTiec);
                cmddelete.ExecuteNonQuery();
                cmddelete.Dispose();
            }
            catch (Exception e5)
            {
                MessageBox.Show(e5.Message);
                _DeleteError = true;
            }
        }
        private void XoaHoaDon(string MaTiec)
        {
            try
            {
                string cmd = "delete from hoadon where matiec = @MATIEC";
                SqlCommand cmddelete = new SqlCommand(cmd, _CNN);
                cmddelete.CommandType = CommandType.Text;
                cmddelete.Parameters.AddWithValue("@MATIEC", MaTiec);
                cmddelete.ExecuteNonQuery();
                cmddelete.Dispose();
            }
            catch (Exception e5)
            {
                MessageBox.Show(e5.Message);
                _DeleteError = true;
            }
        }
        private void XoaTiecCuoi(string MaTiec)
        {
            try
            {
                string cmd = "DELETE FROM TIECCUOI WHERE MATIEC = @MATIEC";
                SqlCommand cmddelete = new SqlCommand(cmd, _CNN);
                cmddelete.CommandType = CommandType.Text;
                cmddelete.Parameters.AddWithValue("@MATIEC", MaTiec);
                cmddelete.ExecuteNonQuery();
                cmddelete.Dispose();
            }
            catch (Exception e5)
            {
                MessageBox.Show(e5.Message);
                _DeleteError = true;
            }
        }
        private void XoaCTTiecCuoi(string MaTiec)
        {
            try
            {
                string cmd = "delete from chitietdattiec where matiec = @MATIEC";
                SqlCommand cmddelete = new SqlCommand(cmd, _CNN);
                cmddelete.CommandType = CommandType.Text;
                cmddelete.Parameters.AddWithValue("@MATIEC", MaTiec);
                cmddelete.ExecuteNonQuery();
                cmddelete.Dispose();
            }
            catch (Exception e5)
            {
                MessageBox.Show(e5.Message);
                _DeleteError = true;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {

        }

        private void button5_Click(object sender, EventArgs e)
        {

        }

        private void B_XemChiTiet_Click(object sender, EventArgs e)
        {
            if (L_viewKQ.FocusedItem == null)
            {
                MessageBox.Show("Vui lòng chọn tiệc cần xem", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.None);
                return;
            }
            string matiec = L_viewKQ.Items[L_viewKQ.FocusedItem.Index].SubItems[0].Text.Trim();
            LayMonAn(matiec);
            LayDichVu(matiec);
            LayHoaDon(matiec);
            CapNhatTienPhat(matiec);

        }

        private void LayMonAn(string MaTiec)
        {
            ListViewItem item;
            bool bool_mau = false;
            listMA.Items.Clear();
            try
            {
                string cmd = "select MONAN.MaMonAn,TenMonAn,DonGia  FROM TIECCUOI,DATBAN,CHITIETMONAN,MONAN WHERE TIECCUOI.MATIEC = DATBAN.MATIEC AND "+
                    "DATBAN.MADATBAN = CHITIETMONAN.MADATBAN AND CHITIETMONAN.MAMONAN = MONAN.MAMONAN AND TIECCUOI.MATIEC = '" +MaTiec+"'" ;
                SqlCommand com = new SqlCommand(cmd, _CNN);
                SqlDataReader read = com.ExecuteReader();
                while (read.Read())
                {
                    item = new ListViewItem(read[0].ToString());
                    item.SubItems.Add(read[1].ToString());
                    item.SubItems.Add(read[2].ToString());
                    if (bool_mau == false)
                        item.BackColor = Color.White;
                    else
                        item.BackColor = Color.FromArgb(245, 204, 102);
                    bool_mau = !bool_mau;
                    listMA.Items.Add(item);
                }
                read.Close();
            }
            catch (Exception e5)
            {
                MessageBox.Show(e5.Message);
    
            }
        }
        private void LayDichVu(string MaTiec)
        {
            ListViewItem item;
            bool bool_mau = false;
            L_viewDV.Items.Clear();
            try
            {
                string cmd = "select DICHVU.MaDichVu,TenDichVu,DonGia  FROM TIECCUOI,DATBAN,CHITIETDICHVU,DICHVU WHERE TIECCUOI.MATIEC = DATBAN.MATIEC AND " +
                    "DATBAN.MADATBAN = CHITIETDICHVU.MADATBAN AND CHITIETDICHVU.MADICHVU = DICHVU.MADICHVU AND TIECCUOI.MATIEC = '" + MaTiec + "'";
                SqlCommand com = new SqlCommand(cmd, _CNN);
                SqlDataReader read = com.ExecuteReader();
                while (read.Read())
                {
                    item = new ListViewItem(read[0].ToString());
                    item.SubItems.Add(read[1].ToString());
                    item.SubItems.Add(read[2].ToString());
                    if (bool_mau == false)
                        item.BackColor = Color.White;
                    else
                        item.BackColor = Color.FromArgb(245, 204, 102);
                    bool_mau = !bool_mau;
                    L_viewDV.Items.Add(item);
                }
                read.Close();
            }
            catch (Exception e5)
            {
                MessageBox.Show(e5.Message);

            }
        }

        private void LayHoaDon(string MaTiec)
        {
            try
            {
                string cmd = "select NgayDaiTiec,NgayThanhToan,TongTienBan,TongTienDichVu,TongTienHoaDon,TienPhat,TongTien"+
                    " FROM TIECCUOI,HOADON WHERE TIECCUOI.MATIEC = HOADON.MATIEC AND TIECCUOI.MATIEC = '" + MaTiec + "'";
                SqlCommand com = new SqlCommand(cmd, _CNN);
                SqlDataReader read = com.ExecuteReader();
                while (read.Read())
                {
                    TB_NgayDatTiec.Text = read[0].ToString();
                    TB_NgaythanhToan.Text = read[1].ToString();

                    TB_Tongtienban.Text = read[2].ToString();
                    TB_Tongtienban.Text = CatString(TB_Tongtienban.Text);
                    TachSo(TB_Tongtienban);
                    TB_TongtienDV.Text = read[3].ToString();
                    TB_TongtienDV.Text = CatString(TB_TongtienDV.Text);
                    TachSo(TB_TongtienDV);
                    TB_ThanhTien.Text = read[4].ToString();
                    TB_ThanhTien.Text = CatString(TB_ThanhTien.Text);

                    TB_Phat.Text = read[5].ToString();
                    TB_DatCoc.Text = (Convert.ToInt64(TB_ThanhTien.Text) / 2).ToString();
                   // TachSo(TB_Phat);
                    TB_TongTienHoaDon.Text = read[6].ToString();
                    TB_TongTienHoaDon.Text = CatString(TB_TongTienHoaDon.Text);
                    long tien = Convert.ToInt64(TB_TongTienHoaDon.Text.Trim()) - Convert.ToInt64(TB_DatCoc.Text.Trim());
                    TB_TienPhaiTRa.Text = tien.ToString();
                    TachSo(TB_TienPhaiTRa);
                    TachSo(TB_ThanhTien);
                    TachSo(TB_TongTienHoaDon);
                    TachSo(TB_DatCoc);

                }
                read.Close();
            }
            catch (Exception e5)
            {
                MessageBox.Show(e5.Message);

            }
        }
        public void TachSo(TextBox luong)
        {
            string txt, txt1;
            txt1 = luong.Text.Replace(",", "");
            txt = "";
            int n = txt1.Length;
            int dem = 0;
            for (int i = n - 1; i >= 0; i--)
            {
                if (dem == 2 && i != 0)
                {
                    txt = "," + txt1.Substring(i, 1) + txt;
                    dem = 0;
                }
                else
                {
                    txt = txt1.Substring(i, 1) + txt;
                    dem += 1;
                }
            }
            luong.Text = txt;
            luong.SelectionStart = luong.Text.Length;
        }

        private void ReSetAllTextBox()
        {
            TB_NgayDatTiec.Text = "";
            TB_NgaythanhToan.Text = "";
            TB_Tongtienban.Text = "";
            TB_TongtienDV.Text = "";
            TB_ThanhTien.Text = "";
            TB_Phat.Text = "";
            TB_TongTienHoaDon.Text = "";
            TB_TienPhaiTRa.Text = "";
            TB_DatCoc.Text = "";

        }

        string CatString(string str)
        {
            int n = str.Length;
            for(int i = 0;i<n;i++)
            {
                if(str[i]=='.')
                {
                    str = str.Substring(0, i);
                    return str;
                }
                
            }
            return str;
        }

        //----------------------------------------------------------------------
        private void CapNhatTienPhat(string MaTiec)
        {
            try
            {
                string cmd = "UPDATE HOADON SET TIENPHAT = TONGTIEN*3/100 WHERE MATIEC IN (SELECT TIECCUOI.MATIEC FROM TIECCUOI,HOADON WHERE TIECCUOI.MATIEC = HOADON.MATIEC AND GETDATE() - NgayThanhToan >= 7 AND TIECCUOI.MATIEC =@MATIEC)";
                SqlCommand cmddelete = new SqlCommand(cmd, _CNN);
                cmddelete.CommandType = CommandType.Text;
                cmddelete.Parameters.AddWithValue("@MATIEC", MaTiec);
                cmddelete.ExecuteNonQuery();
                cmddelete.Dispose();
            }
            catch (Exception e5)
            {
                MessageBox.Show(e5.Message);
            }
        }
      

    }
}
